package cn.edu.cqupt.cxcy.dto;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Statement;

import cn.edu.cqupt.cxcy.dao.TeamDao;
import cn.edu.cqupt.cxcy.model.Member;
import cn.edu.cqupt.cxcy.model.MemberInfo;
import cn.edu.cqupt.cxcy.model.Project;
import cn.edu.cqupt.cxcy.model.Team;
import cn.edu.cqupt.cxcy.model.Teamunite;
import cn.edu.cqupt.cxcy.model.Tutor;
import cn.edu.cqupt.cxcy.util.Conn;

public class TeamImpl implements TeamDao{
	/**
	 * 将团队发布的项目添加到数据库
	 */
	@Override
	public void publishProject(Project project) {
		Connection conn = null;
		PreparedStatement statement = null;
		String sql = "insert into project(p_name,p_ctime,p_progress,p_explain,p_content) values(?,now(),?,?,?)";
		try{
			conn =Conn.getConnect();
			statement = conn.prepareStatement(sql);
			statement.setString(1, project.getP_name());
			statement.setInt(2, project.getP_progress());
			statement.setString(3, project.getP_explain());
			statement.setString(4, project.getP_content());//team/pubpro
			statement.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Conn.close(null, statement, conn);
		}
		
	}
    /**
     * 得到团队成员的基本信息
     */
	@Override
	public List<MemberInfo> showTeamMember(int id) {
		// TODO Auto-generated method stub
		List<MemberInfo> members = new ArrayList<MemberInfo>();
		ResultSet set = null;
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "SELECT member.* from member,teamunite WHERE member.m_id = teamunite.m_id and teamunite.te_id = ?";
		try{
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			set = ps.executeQuery();
			while(set.next()){
				MemberInfo member = new MemberInfo();
				member.setM_name(set.getString("m_name"));
				member.setM_logo(set.getString("m_logo"));
				member.setM_type(set.getString("tun_type"));
				members.add(member);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Conn.close(null, ps, conn);
		}
		return members;
	}
    /**
     * 得到团队成员的人数
     */
	@Override
	public int getTeamMemberCount(int id) {
		// TODO Auto-generated method stub
		int count = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet set = null;
		String sql = "select count(*) as count from teamUnite where te_id = ?";
		try{
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			set = ps.executeQuery();
			while(set.next()){
				count = set.getInt("count");
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Conn.close(null, ps, conn);
		}
		return count;
	}
	/**
	 * 添加成员
	 */
	@Override
	public void addTeamMember(Member member,int teamId,String type) {
		// TODO Auto-generated method stub
		Connection connection = null;
		PreparedStatement ps =null;
		ResultSet set = null;
		//首先判断member中是否存在该成员
		String sql1 = "select * from member where m_id = ?";
		String sql2 = "insert into member values(?,?,?,?,?,?,?,?,?,?,?,?)";
		String sql3 = "insert into teamunite values(?,?,?)";
		try{
			connection = Conn.getConnect();
			ps = connection.prepareStatement(sql1);
			ps.setInt(1, member.getM_id());
			if((set = ps.executeQuery()).next()){//说明存在该成员,直接添加到teamunite即可
				
			}else{
				ps = connection.prepareStatement(sql2);
				ps.setString(1, member.getM_name());
				ps.setString(2, member.getM_logo());
				ps.setString(3, member.getM_sex());
				ps.setDate(4, (Date) member.getM_birth());
				ps.setString(5, member.getM_tel());
				ps.setString(6, member.getM_no());
				ps.setString(7, member.getM_xl());
				ps.setDate(8, (Date) member.getM_ctime());
				ps.executeUpdate();
				
			}
			ps = connection.prepareStatement(sql3);
			ps.setInt(1, teamId);
			ps.setInt(2, member.getM_id());
			ps.setString(3, type);
			ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Conn.close(null, ps, connection);
		}
	}
	/**
	 * 删除成员
	 */
	@Override
	public void deleteTeamMember(int id) {
		// TODO Auto-generated method stub
		Connection connection = null;
		PreparedStatement ps = null;
		String sql = "delete from teamunite where teamunite.m_id = ?";//只需要删除teamunite表中的成员即可
		try{
			connection = Conn.getConnect();
			ps = connection.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Conn.close(null, ps, connection);
		}
	}  
	/**
	 * 以列表形式展示团队成员
	 */
	@Override
	public List<Object> showTeamMemberByList(int id) {
		// TODO Auto-generated method stub
		List<Object> list = new ArrayList<Object>();
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet set = null;
		String sql = "select member.*,teamunite.tun_type from member,teamunite where member.m_id = teamunite.m_id and teamunite.te_id = ?";
		try{
			connection = Conn.getConnect();
			ps = connection.prepareStatement(sql);
			ps.setInt(1, id);
			set = ps.executeQuery();
			while(set.next()){
			    Member member = new Member();
			    Teamunite teamunite = new Teamunite();
			    member.setM_name(set.getString("m_name"));
			    member.setM_tel(set.getString("m_tel"));
			    member.setM_sex(set.getString("m_sex"));
			    teamunite.setTun_type(set.getString("tun_type"));
			    list.add(member);
			    list.add(teamunite);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Conn.close(set, ps,connection);
			return list;
		}
	}
	/**
	 * 展示团队信息的当页总条数
	 * @return
	 */
	public static int currentPageSum(int sum_count,int current_page,int sum_pages){
		if(current_page != sum_pages){
			return 10;
		}else{
			return sum_count%10;
		}
	}
	/**
	 * 保存发布的项目信息
	 */
	@Override
	public void insertProjectInfo(Project project) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement statement = null;
		String sql = "insert into project(p_name,p_logo,p_ctime,p_progress,p_content,p_explain,te_id,p_filename,p_filepath,pt_id) values(?,?,now(),?,?,?,?,?,?,?)";
	    try{
	    	conn = Conn.getConnect();
	    	statement = conn.prepareStatement(sql);
	    	statement.setString(1, project.getP_name());
	    	statement.setString(2, project.getP_logo());
	    	statement.setInt(3,0);
	    	statement.setString(4, project.getP_content());
	    	statement.setString(5, project.getP_explain());
	    	statement.setInt(6, project.getTe_id());
	    	statement.setString(7, project.getP_filename());
	    	statement.setString(8, project.getP_filepath());
	    	statement.setInt(9, project.getPt_id());
	    	statement.executeUpdate();
	    }catch(Exception e){
	    	e.printStackTrace();
	    }finally{
	    	Conn.close(null, statement, conn);
	    }
	}
	/**
	 * 得到团队导师的信息
	 */
	@Override
	public Tutor tutorInfo(int id) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement statement = null;
		Tutor tutor = null;
		ResultSet set = null;
		//String sql = "select distinct tutor.tu_loname,tutor.tu_name,tutor.tu_logo,tutor.tu_tel,tutor.gd_name,tutor.tu_explain from teamTutor,tutor,team where tutor.tu_id in(select teamtutor.tu_id from teamtutor where teamtutor.te_id = ?)";
		String sql = "select distinct tutor.gd_id,tutor.tu_loname,tutor.tu_name,tutor.tu_logo,tutor.tu_tel,tutor.tu_explain,guiDirection.gd_name,guiDirection.gd_explain from teamTutor,tutor,team,guiDirection where tutor.tu_id in(select teamtutor.tu_id from teamtutor where teamtutor.te_id = ?) and tutor.gd_id = guiDirection.gd_id and teamTutor.tt_teamcheck = 2 and teamTutor.tt_tutorcheck = 2";
		try{
			conn = Conn.getConnect();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, id);
			set = statement.executeQuery();
			if(set.first()){
				tutor = new Tutor();
				tutor.setTu_loname(set.getString("tu_loname"));
				tutor.setTu_name(set.getString("tu_name"));
				tutor.setTu_logo(set.getString("tu_logo"));
				tutor.setTu_explain(set.getString("tu_explain"));//teamTutor.te_id and teamTutor.tu_id = tutor.tu_id
				tutor.setTu_tel(set.getString("tu_tel"));
				tutor.setGd_id(set.getInt("gd_id"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Conn.close(set, statement, conn);
		}
		return tutor;
	}
	/**
	 * 得到团队的基本信息
	 */
	@Override
	public Team teamInfo(int teamId) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet set = null;
		Team team = null;
		String sql = "select * from team where te_id = ?";
		try{
			conn = Conn.getConnect();
			statement = conn.prepareStatement(sql);
			statement.setInt(1, teamId);
			set = statement.executeQuery();
			if(set.first()){
			 team = new Team();
			 team.setTe_logo(set.getString("te_logo"));
			 team.setTe_name(set.getString("te_name"));
			 team.setTe_explain(set.getString("te_explain"));
			 team.setTe_ctime(set.getDate("te_ctime"));
			 //team.setTe_ctime(set.getDate("te_ctime"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return team;
	}
	/**
	 * 保存成员的信息到数据库
	 */
	@Override
	public void addMemberInfo(Member member,int teamId) {
		Connection conn = null;
		PreparedStatement statement1 = null;
		PreparedStatement statement2 = null;
		ResultSet key = null;
		System.out.println("memberid:"+member.getM_id());
		String sql1 = "insert into member(m_name,m_sex,m_NO,m_tel,m_email,m_school,m_college,m_explain,m_logo) values(?,?,?,?,?,?,?,?,?)";
		String sql2 = "insert into teamUnite(te_id,m_id,tun_type) values(?,?,?)";
		String sql3 = "update member set m_name = ?,m_sex = ?,m_NO = ?,m_tel = ?,m_email = ?,m_school = ?,m_college = ?,m_explain = ?,m_logo = ? where m_id = ?";
		String sql4 = "update teamUnite set tun_type = ? where m_id = ? and te_id = ?";
		String sql5 = "update member set m_name = ?,m_sex = ?,m_NO = ?,m_tel = ?,m_email = ?,m_school = ?,m_college = ?,m_explain = ? where m_id = ?";
		try{
			conn = Conn.getConnect();
			if(member.getM_id() == 0){
			statement1 = conn.prepareStatement(sql1,Statement.RETURN_GENERATED_KEYS);
			statement1.setString(1, member.getM_name());
			statement1.setString(2, member.getM_sex());
			statement1.setString(3, member.getM_no());
			statement1.setString(4, member.getM_tel());
			statement1.setString(5, member.getM_email());
			if(member.getM_school() != null){
			statement1.setString(6, member.getM_school());
			}else{
				statement1.setString(6, "");
			}
			if(member.getM_college() != null){
			statement1.setString(7, member.getM_college());
			}else{
				statement1.setString(7, "");
			}
			statement1.setString(8, member.getM_explain());
			statement1.setString(9, member.getM_logo());
			statement1.execute();
			key = statement1.getGeneratedKeys();
			key.next();
			String memberId = key.getString(1);
			statement2 = conn.prepareStatement(sql2);
			statement2.setInt(1, teamId);
			statement2.setInt(2, Integer.valueOf(memberId));
			statement2.setString(3, member.getM_type());//添加的队员的身份
			statement2.executeUpdate();
			}else{
				if(member.getM_logo() != null){
				statement1 = conn.prepareStatement(sql3);
				statement1.setString(1, member.getM_name());
				statement1.setString(2, member.getM_sex());
				statement1.setString(3, member.getM_no());
				statement1.setString(4, member.getM_tel());
				statement1.setString(5, member.getM_email());
				if(member.getM_school() != null){
				statement1.setString(6, member.getM_school());
				}else{
					statement1.setString(6, "");
				}
				if(member.getM_college() != null){
				statement1.setString(7, member.getM_college());
				}else{
					statement1.setString(7, "");
				}
				statement1.setString(8, member.getM_explain());
				statement1.setString(9, member.getM_logo());
				statement1.setInt(10, member.getM_id());
				statement1.executeUpdate();
				}else{
					statement1 = conn.prepareStatement(sql5);
					statement1.setString(1, member.getM_name());
					statement1.setString(2, member.getM_sex());
					statement1.setString(3, member.getM_no());
					statement1.setString(4, member.getM_tel());
					statement1.setString(5, member.getM_email());
					if(member.getM_school() != null){
					statement1.setString(6, member.getM_school());
					}else{
						statement1.setString(6, "");
					}
					if(member.getM_college() != null){
					statement1.setString(7, member.getM_college());
					}else{
						statement1.setString(7, "");
					}
					statement1.setString(8, member.getM_explain());
					statement1.setInt(9, member.getM_id());
					statement1.executeUpdate();
				}
				statement2 = conn.prepareStatement(sql4);
				statement2.setString(1, member.getM_type());//添加的队员的身份
				statement2.setInt(2, member.getM_id());
				statement2.setInt(3, teamId);
				statement2.executeUpdate();
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Conn.close(key, statement1, conn);
			Conn.close(null, statement2, conn);
		}
	}
	/**
	 * 以图片形式展示团队成员
	 */
	@Override
	public List<Object> showTeamMemberByPicture(int id) {
		// TODO Auto-generated method stub
		List<Object> list = new ArrayList<Object>();
		Connection connection = null;
		PreparedStatement ps = null;
		ResultSet set = null;
		String sql = "select member.*,teamunite.tun_type from member,teamunite where member.m_id = teamunite.m_id and teamunite.te_id = ?";
		try{
			connection = Conn.getConnect();
			ps = connection.prepareStatement(sql);
			ps.setInt(1, id);
			set = ps.executeQuery();
			while(set.next()){
			    Member member = new Member();
			    Teamunite teamunite = new Teamunite();
			    if(set.getString("m_name") != null){
			    member.setM_name(set.getString("m_name"));
			    }else{
			    	member.setM_name("");
			    }
			    if(set.getString("m_tel") != null){
			    member.setM_tel(set.getString("m_tel"));
			    }else{
			    	member.setM_tel("");
			    }
			    if(set.getString("m_sex") != null){
			    member.setM_sex(set.getString("m_sex"));
			    }else{
			    	member.setM_sex("");
			    }
			    if(set.getString("m_NO") != null){
			    member.setM_no(set.getString("m_NO"));
			    }else{
			    	member.setM_no("");
			    }
			    if(set.getString("m_email") != null){
			    member.setM_email(set.getString("m_email"));
			    }else{
			    	member.setM_email("");
			    }
			    if(set.getString("m_school") != null){
			    member.setM_school(set.getString("m_school"));
			    }else{
			    	member.setM_school("");
			    }
			    if(set.getString("m_college") != null){
			    member.setM_college(set.getString("m_college"));
			    }else{
			    	member.setM_college("");
			    }
			    if(set.getString("m_explain") != null){
			    	member.setM_explain(set.getString("m_explain"));
			    }else{
			    	member.setM_explain("");
			    }
			    if(set.getString("m_xl") != null){
			    	member.setM_xl(set.getString("m_xl"));
			    }else{
			    	member.setM_xl("");
			    }
			    if(set.getString("m_logo") != null){
			    	member.setM_logo(set.getString("m_logo"));
			    }else{
			    	member.setM_logo("");
			    }
			    member.setM_id(set.getInt("m_id"));
			    member.setM_ctime(set.getDate("m_ctime"));
			    if(set.getString("tun_type") != null){
			    	 teamunite.setTun_type(set.getString("tun_type"));
			    }else{
			    	teamunite.setTun_type("");
			    }
			    list.add(member);
			    list.add(teamunite);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Conn.close(set, ps,connection);
			return list;
		}
	}
    /**
     * 完善团队的信息
     */
	@Override
	public void addTeamInfo(Team team) {
		Connection conn = null;
		PreparedStatement statement = null;
		//String sql = "insert into team(te_name,te_logo,te_explain) values(?,?,?) where te_id = ?";
		String sql = "update team set te_name = ?,te_logo = ?,te_explain = ? where te_id = ?";
		String sql1 = "update team set te_name = ?,te_explain = ? where te_id = ?";
		try{
			conn = Conn.getConnect();
			if(team.getTe_logo() != null){
			statement = conn.prepareStatement(sql);
			statement.setString(1, team.getTe_name());
			statement.setString(2, team.getTe_logo());
			statement.setString(3, team.getTe_explain());
			statement.setInt(4, team.getTe_id());
			statement.executeUpdate();
			}else{
				statement = conn.prepareStatement(sql1);
				statement.setString(1, team.getTe_name());
				statement.setString(2, team.getTe_explain());
				statement.setInt(3, team.getTe_id());
				statement.executeUpdate();
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Conn.close(null, statement, conn);
		}
	}
	/**
	 * 根据队员id得到队员的信息
	 */
	@Override
	public Member getMemberInfoById(int memberId) {
		Connection conn = null;
		PreparedStatement statement1 = null;
		PreparedStatement statement2 = null;
		Member member = new Member();
		ResultSet result1 = null;
		ResultSet result2 = null;
		String sql1 = "select * from member where m_id = ?";
		String sql2 = "select tun_type from teamUnite where m_id = ?";
		try{
			conn = Conn.getConnect();
			statement1 = conn.prepareStatement(sql1);
			statement1.setInt(1, memberId);
			result1 = statement1.executeQuery();
			
			if(result1.next()){
				member.setM_id(result1.getInt("m_id"));
				if(result1.getString("m_name") != null){
				member.setM_name(result1.getString("m_name"));
				}else{
					member.setM_name("");
				}
				if(result1.getString("m_logo") != null){
				member.setM_logo(result1.getString("m_logo"));
				}else{
					member.setM_logo("");
				}
				if(result1.getString("m_sex") != null){
				member.setM_sex(result1.getString("m_sex"));
				}else{
					member.setM_sex("");
				}
				if(result1.getString("m_NO") != null){
				member.setM_no(result1.getString("m_NO"));
				}else{
					member.setM_no("");
				}
				if(result1.getString("m_tel") != null){
				member.setM_tel(result1.getString("m_tel"));
				}else{
					member.setM_tel("");
				}
				if(result1.getString("m_school") != null){
				member.setM_school(result1.getString("m_school"));
				}else{
					member.setM_school("");
				}
				if(result1.getString("m_college") != null){
				member.setM_college(result1.getString("m_college"));
				}else{
					member.setM_college("");
				}
				if(result1.getString("m_explain") != null){
				member.setM_explain(result1.getString("m_explain"));
				}else{
					member.setM_explain("");
				}
				if(result1.getString("m_email") != null){
				member.setM_email(result1.getString("m_email"));
				}else{
					member.setM_email("");
				}
			}
			statement2 = conn.prepareStatement(sql2);
			statement2.setInt(1, memberId);
			result2 = statement2.executeQuery();
			if(result2.next()){
				if(result2.getString("tun_type") != null){
				member.setM_type(result2.getString("tun_type"));
				}else{
					member.setM_type("");
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Conn.close(result1, statement1, conn);
			Conn.close(result2, statement2, conn);
			return member;
		}
	}
	@Override
	public boolean deleteTeamMenber(int memberId) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean isSuccess = false;
		String sql1 = "delete from member where m_id = ?";
		//String sql2 = "delete from teamUnite where m_id = ?";
		int result;
		try{
			conn =Conn.getConnect();
			ps = conn.prepareStatement(sql1);
			ps.setInt(1, memberId);
			result = ps.executeUpdate();
			if(result > 0){
				isSuccess = true;
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Conn.close(null, ps, conn);
			return isSuccess;
		}
	}
	@Override
	public int getTypeByName(String type) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet set = null;
		int t_type = 0;
		String sql = "select * from projectype where pt_name = ?";
	    try{
	    	conn = Conn.getConnect();
	    	statement = conn.prepareStatement(sql);
	    	statement.setString(1, type);
	    	set = statement.executeQuery();
	    	if(set.next()){
	    		t_type = set.getInt("pt_id");
	    	}
	    }catch(Exception e){
	    	e.printStackTrace();
	    }finally{
	    	Conn.close(set, statement, conn);
	    }
		return t_type;
	}

	
}
